/******************************************************************************/
/***          Generated by IBExpert 2009.01.16 31.10.2010 18:07:51          ***/
/******************************************************************************/



/******************************************************************************/
/***                               Generators                               ***/
/******************************************************************************/

CREATE SEQUENCE GEN_INDEXER_DATES_ID;
CREATE SEQUENCE GEN_INDEXER_LEMMAS_ID;
CREATE SEQUENCE GEN_INDEXER_NORMALIZE_FORM_ID;
CREATE SEQUENCE GEN_INDEXER_WORDS_ID;
CREATE SEQUENCE GEN_INDEXER_WORDS_INDEX_ID;
CREATE SEQUENCE GEN_LANG_LIST_ID;


/******************************************************************************/
/***                               Exceptions                               ***/
/******************************************************************************/

CREATE EXCEPTION BAD_VALUE 'Не правильное значение';



SET TERM ^ ; 



/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/

CREATE OR ALTER PROCEDURE GET_ALLOWED_FORMS (
    LEN INTEGER,
    WORD VARCHAR(100),
    PREFIX VARCHAR(10))
RETURNS (
    BASE VARCHAR(50),
    SUFFIX VARCHAR(50),
    NUM_RULE INTEGER,
    ANCODE VARCHAR(10))
AS
BEGIN
  SUSPEND;
END^


CREATE OR ALTER PROCEDURE SAVE_LEMMA (
    NUM_RULE INTEGER NOT NULL,
    NUM_ACCENT INTEGER NOT NULL,
    ANCODE VARCHAR(4),
    NUM_PREFIX INTEGER,
    ID_LANG INTEGER NOT NULL,
    LEMMA VARCHAR(50) NOT NULL)
AS
BEGIN
  EXIT;
END^



SET TERM ; ^



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE INDEXER_ACCENTS (
    ID       BIGINT NOT NULL,
    NUM      INTEGER NOT NULL,
    POS      INTEGER NOT NULL,
    ACCENT   SMALLINT NOT NULL,
    ID_LANG  INTEGER
);

CREATE TABLE INDEXER_ANCODE (
    ID              BIGINT NOT NULL,
    ANCODE          VARCHAR(2) NOT NULL,
    ABBR            CHAR(1) NOT NULL,
    PART_OF_SPEECH  VARCHAR(12) NOT NULL,
    GRAMEMS         VARCHAR(35) NOT NULL,
    ID_LANG         INTEGER NOT NULL
);

CREATE TABLE INDEXER_DATES (
    ID         BIGINT NOT NULL,
    ID_HEADER  INTEGER NOT NULL,
    ID_LINK    INTEGER NOT NULL,
    "YEAR"     SMALLINT,
    "MONTH"    SMALLINT,
    "DAY"      SMALLINT,
    "HOUR"     SMALLINT,
    "MINUTE"   SMALLINT,
    WEEKDAY    SMALLINT,
    YEARDAY    SMALLINT
);

CREATE TABLE INDEXER_LEMMAS (
    ID          BIGINT NOT NULL,
    LEMMA       VARCHAR(50) NOT NULL,
    NUM_RULE    INTEGER NOT NULL,
    NUM_ACCENT  INTEGER NOT NULL,
    ID_ANCODE   BIGINT,
    ID_PREFIX   INTEGER,
    ID_LANG     INTEGER NOT NULL
);

CREATE TABLE INDEXER_NORMALIZE_FORM (
    ID              BIGINT NOT NULL,
    NORMALIZE_FORM  VARCHAR(255) NOT NULL,
    LANG            INTEGER NOT NULL
);

CREATE TABLE INDEXER_POSSIBLE_PREFIXES (
    ID        BIGINT NOT NULL,
    ID_RULES  BIGINT NOT NULL,
    ID_LANG   INTEGER NOT NULL
);

CREATE TABLE INDEXER_PREFIXES (
    ID        BIGINT NOT NULL,
    NUM       SMALLINT NOT NULL,
    PREFIXES  VARCHAR(15) NOT NULL,
    ID_LANG   INTEGER NOT NULL
);

CREATE TABLE INDEXER_RULES (
    ID       BIGINT NOT NULL,
    NUM      INTEGER NOT NULL,
    POS      SMALLINT NOT NULL,
    ID_LANG  INTEGER NOT NULL,
    SUFFIX   VARCHAR(50) NOT NULL,
    ANCODE   VARCHAR(10) NOT NULL,
    PREFIX   VARCHAR(10),
    INFO     VARCHAR(50)
);

CREATE TABLE INDEXER_WORDS (
    ID                 BIGINT NOT NULL,
    WORD               VARCHAR(300) NOT NULL,
    LANG               INTEGER NOT NULL,
    ID_NORMALIZE_FORM  BIGINT
);

CREATE TABLE INDEXER_WORDS_INDEX (
    ID                 BIGINT NOT NULL,
    ID_HEADER          INTEGER NOT NULL,
    ID_LINK            INTEGER NOT NULL,
    ID_NORMALIZE_FORM  BIGINT NOT NULL
);

CREATE TABLE LANG_LIST (
    ID         INTEGER NOT NULL,
    LANG_NAME  VARCHAR(100) NOT NULL,
    LNG        VARCHAR(10) NOT NULL
);



/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE INDEXER_ACCENTS ADD CONSTRAINT PK_INDEXER_ACCENTS PRIMARY KEY (ID);
ALTER TABLE INDEXER_ANCODE ADD CONSTRAINT PK_INDEXER_ANCODE PRIMARY KEY (ID);
ALTER TABLE INDEXER_DATES ADD CONSTRAINT PK_INDEXER_DATES PRIMARY KEY (ID);
ALTER TABLE INDEXER_LEMMAS ADD CONSTRAINT PK_INDEXER_LEMMAS PRIMARY KEY (ID);
ALTER TABLE INDEXER_NORMALIZE_FORM ADD CONSTRAINT PK_INDEXER_NORMALIZE_FORM PRIMARY KEY (ID);
ALTER TABLE INDEXER_POSSIBLE_PREFIXES ADD PRIMARY KEY (ID);
ALTER TABLE INDEXER_PREFIXES ADD CONSTRAINT PK_INDEXER_PREFIXES PRIMARY KEY (ID);
ALTER TABLE INDEXER_RULES ADD PRIMARY KEY (ID);
ALTER TABLE INDEXER_WORDS ADD PRIMARY KEY (ID);
ALTER TABLE INDEXER_WORDS_INDEX ADD CONSTRAINT PK_INDEXER_WORDS_INDEX PRIMARY KEY (ID);
ALTER TABLE LANG_LIST ADD CONSTRAINT PK_LANG_LIST PRIMARY KEY (ID);


/******************************************************************************/
/***                              Foreign Keys                              ***/
/******************************************************************************/

ALTER TABLE INDEXER_ACCENTS ADD CONSTRAINT FK_INDEXER_ACCENTS_LANG FOREIGN KEY (ID_LANG) REFERENCES LANG_LIST (ID) ON UPDATE CASCADE;
ALTER TABLE INDEXER_ANCODE ADD CONSTRAINT FK_INDEXER_ANCODE_LANG FOREIGN KEY (ID_LANG) REFERENCES LANG_LIST (ID) ON UPDATE CASCADE;
ALTER TABLE INDEXER_LEMMAS ADD CONSTRAINT FK_INDEXER_LEMMAS_LANG FOREIGN KEY (ID_LANG) REFERENCES LANG_LIST (ID) ON UPDATE CASCADE;
ALTER TABLE INDEXER_POSSIBLE_PREFIXES ADD CONSTRAINT FK_INDEXER_POSSIBLE_PREFIXES_1 FOREIGN KEY (ID_LANG) REFERENCES LANG_LIST (ID) ON UPDATE CASCADE;
ALTER TABLE INDEXER_POSSIBLE_PREFIXES ADD CONSTRAINT FK_INDEXER_POSSIBLE_PREFIXES_2 FOREIGN KEY (ID_RULES) REFERENCES INDEXER_RULES (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE INDEXER_RULES ADD CONSTRAINT FK_INDEXER_RULES_LANG FOREIGN KEY (ID_LANG) REFERENCES LANG_LIST (ID) ON UPDATE CASCADE;


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX INDEXER_ANCODE_ANCODE ON INDEXER_ANCODE (ANCODE, ID_LANG);
CREATE INDEX INDEXER_LEMMAS_IDX1 ON INDEXER_LEMMAS (LEMMA);
CREATE INDEX INDEXER_POSSIBLE_PREFIXES_PREFI ON INDEXER_POSSIBLE_PREFIXES (ID_RULES, ID_LANG);
CREATE INDEX INDEXER_PREFIXES_PREFIX ON INDEXER_PREFIXES (PREFIXES);
CREATE UNIQUE INDEX INDEXER_PREFIXES_UNQ ON INDEXER_PREFIXES (NUM, ID_LANG);
CREATE INDEX INDEXER_RULES_IDX1 ON INDEXER_RULES (NUM, ID_LANG);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: INDEXER_ACCENTS_BI */
CREATE OR ALTER TRIGGER INDEXER_ACCENTS_BI FOR INDEXER_ACCENTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_indexer_lemmas_id,1);
end
^

/* Trigger: INDEXER_ANCODE_BI */
CREATE OR ALTER TRIGGER INDEXER_ANCODE_BI FOR INDEXER_ANCODE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_INDEXER_LEMMAS_ID,1);
END
^

/* Trigger: INDEXER_DATES_BI */
CREATE OR ALTER TRIGGER INDEXER_DATES_BI FOR INDEXER_DATES
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_INDEXER_DATES_id,1);
end
^

/* Trigger: INDEXER_LEMMAS_BI */
CREATE OR ALTER TRIGGER INDEXER_LEMMAS_BI FOR INDEXER_LEMMAS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_indexer_lemmas_id,1);
end
^

/* Trigger: INDEXER_NORMALIZE_FORM_BI */
CREATE OR ALTER TRIGGER INDEXER_NORMALIZE_FORM_BI FOR INDEXER_NORMALIZE_FORM
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_INDEXER_NORMALIZE_FORM_ID,1);
END
^

/* Trigger: INDEXER_POSSIBLE_PREFIXES_BI */
CREATE OR ALTER TRIGGER INDEXER_POSSIBLE_PREFIXES_BI FOR INDEXER_POSSIBLE_PREFIXES
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_INDEXER_LEMMAS_ID,1);
END
^

/* Trigger: INDEXER_PREFIXES_BI */
CREATE OR ALTER TRIGGER INDEXER_PREFIXES_BI FOR INDEXER_PREFIXES
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_indexer_lemmas_id,1);
end
^

/* Trigger: INDEXER_RULES_AIU0_PREFIXES */
CREATE OR ALTER TRIGGER INDEXER_RULES_AIU0_PREFIXES FOR INDEXER_RULES
ACTIVE AFTER INSERT POSITION 0
AS
begin
  if (new.prefix<>'')  then
     INSERT INTO indexer_possible_prefixes (id_rules, id_lang) values (new.id,  new.id_lang);
end
^

/* Trigger: INDEXER_RULES_BI */
CREATE OR ALTER TRIGGER INDEXER_RULES_BI FOR INDEXER_RULES
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_INDEXER_LEMMAS_ID,1);
  if ((new.ancode<>'') and
  (not exists(select ancode from indexer_ancode where ancode=new.ancode and id_lang=new.id_lang) ) )
  then exception bad_value ;
END
^

/* Trigger: INDEXER_WORDS_BI */
CREATE OR ALTER TRIGGER INDEXER_WORDS_BI FOR INDEXER_WORDS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_INDEXER_WORDS_ID,1);
END
^

/* Trigger: INDEXER_WORDS_INDEX_BI */
CREATE OR ALTER TRIGGER INDEXER_WORDS_INDEX_BI FOR INDEXER_WORDS_INDEX
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_indexer_words_index_id,1);
end
^

/* Trigger: LANG_LIST_BI */
CREATE OR ALTER TRIGGER LANG_LIST_BI FOR LANG_LIST
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_LANG_LIST_ID,1);
END
^

SET TERM ; ^



/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/


SET TERM ^ ;

CREATE OR ALTER PROCEDURE GET_ALLOWED_FORMS (
    LEN INTEGER,
    WORD VARCHAR(100),
    PREFIX VARCHAR(10))
RETURNS (
    BASE VARCHAR(50),
    SUFFIX VARCHAR(50),
    NUM_RULE INTEGER,
    ANCODE VARCHAR(10))
AS
declare variable i integer;
declare variable pos integer;
declare variable id_lang smallint;
begin
  i=:len;
  if (:prefix is null) then prefix='';
  while (len>0)
  do
  begin
     base=left(:word, :len);
     suffix=reverse(left(reverse(:word), i-len));
     len=:len-1;

     for
        select  --indexer_rules.prefix,  indexer_lemmas.lemma, indexer_rules.suffix,indexer_rules.id,
            indexer_rules.pos , indexer_rules.num,indexer_rules.id_lang ,indexer_rules.ancode
        from indexer_rules
            inner join indexer_lemmas on (indexer_rules.id_lang = indexer_lemmas.id_lang) and (indexer_rules.num = indexer_lemmas.num_rule)
        where ((indexer_lemmas.lemma = :base) and (indexer_rules.suffix = :suffix) and (indexer_rules.prefix=:prefix))
        into
        :pos,
        :num_rule,
        :id_lang,
        :ancode
        do
            begin
                if (:num_rule is not null) then
                begin
                  if (:pos>0) then
                    select indexer_rules.suffix,indexer_rules.ancode from indexer_rules
                    where ((indexer_rules.num = :num_rule) and (indexer_rules.pos = 0) and (indexer_rules.id_lang = :id_lang))
                    into :suffix, :ancode;
                  suspend;
                end
            end
  end
  base='';
  for select  --indexer_rules.prefix,  indexer_lemmas.lemma, indexer_rules.suffix,indexer_rules.id,
            indexer_rules.pos , indexer_rules.num,indexer_rules.id_lang
  from indexer_rules
  where ((indexer_rules.suffix = :word) and (indexer_rules.prefix=:prefix))
  into
        :pos,
        :num_rule,
        :id_lang
  do
    begin
        if (:num_rule is not null) then
        begin
            if (pos>0) then
                    select indexer_rules.suffix,indexer_rules.ancode from indexer_rules
                    where ((indexer_rules.num = :num_rule) and (indexer_rules.pos = 0) and (indexer_rules.id_lang = :id_lang))
                    into :suffix, :ancode;

            suspend;
        end
   end
end^


CREATE OR ALTER PROCEDURE SAVE_LEMMA (
    NUM_RULE INTEGER NOT NULL,
    NUM_ACCENT INTEGER NOT NULL,
    ANCODE VARCHAR(4),
    NUM_PREFIX INTEGER,
    ID_LANG INTEGER NOT NULL,
    LEMMA VARCHAR(50) NOT NULL)
AS
declare variable id_rule integer;
declare variable id_accent integer;
declare variable id_ancode integer;
declare variable id_prefix integer;
declare variable suffix varchar(50);
declare variable prefix varchar(10);
declare variable pos smallint;
declare variable endings varchar(4);
declare variable len smallint;
declare variable id_endings bigint;
declare variable id_endings_pos bigint;
begin

  /*select id from INDEXER_ACCENTS  where num=:num_accent and id_lang=:id_lang
    into :id_accent; $$IBEC$$*/
  select id from INDEXER_ANCODE where ancode=:ancode and id_lang=:id_lang
    into :id_ancode;
  select id from indexer_prefixes where num=:num_prefix and id_lang=:id_lang
    into :id_prefix;
  insert into indexer_lemmas(lemma, num_rule,num_accent,id_ancode,id_prefix, id_lang)
    values(:lemma,:num_rule,:num_accent,:id_ancode,:id_prefix,:id_lang);
  /*for
    select indexer_rules.pos, indexer_rules.suffix, indexer_rules.prefix
    from indexer_rules
    where ((indexer_rules.num =:num_rule) and (indexer_rules.id_lang = :id_lang))
    into
        :pos,
        :suffix,
        :prefix
  do
    begin
       len=4;
       endings=reverse(left(reverse(:prefix||:lemma||:suffix),4));
       while(:len>0) do
        begin

            select id from indexer_endings where endings=:endings and id_lang=:id_lang into :id_endings;
            if (:id_endings is null) then
                begin
                    id_endings=gen_id(gen_indexer_lemmas_id,1);
                    insert into indexer_endings(id,endings,id_lang) values (:id_endings, :endings,:id_lang);
                end
            select id from indexer_endings_rules where rule_num=:num_rule and id_lang=:id_lang
            into :id_rule;
            if (:id_rule is null) then
                begin
                    id_rule=gen_id(gen_indexer_lemmas_id,1);
                    insert into indexer_endings_rules(id,id_endings,rule_num,id_lang)
                        values(:id_rule,:id_endings,:num_rule,:id_lang);
                end
            select id from indexer_endings_rules_pos where id_endings_rules=:id_rule and id_lang=:id_lang
            into :id_endings_pos;
            if (:id_endings_pos is null) then
                begin
                    id_endings_pos=gen_id(gen_indexer_lemmas_id,1);
                    insert into indexer_endings_rules_pos(id,id_endings_rules,pos,id_lang)
                        values(:id_endings_pos,:id_rule,:pos,:id_lang);
                end
            len=:len-1;
            endings=right(:endings, :len);
         end
     end  */
end^



SET TERM ; ^


/******************************************************************************/
/***                              Descriptions                              ***/
/******************************************************************************/

COMMENT ON TABLE INDEXER_WORDS_INDEX IS 
'Normalize index words table for search engine';



/* Fields descriptions */

COMMENT ON COLUMN INDEXER_NORMALIZE_FORM.NORMALIZE_FORM IS 
'word normalize form';



/******************************************************************************/
/***                               Privileges                               ***/
/******************************************************************************/


/* Privileges of users */
GRANT SELECT ON RDB$FORMATS TO PUBLIC;
GRANT SELECT ON RDB$PAGES TO PUBLIC;
GRANT SELECT ON RDB$ROLES TO PUBLIC;

/* Privileges of procedures */
GRANT SELECT ON INDEXER_ANCODE TO PROCEDURE SAVE_LEMMA;
GRANT INSERT ON INDEXER_LEMMAS TO PROCEDURE SAVE_LEMMA;
GRANT SELECT ON INDEXER_PREFIXES TO PROCEDURE SAVE_LEMMA;
GRANT SELECT ON INDEXER_RULES TO PROCEDURE SAVE_LEMMA;
